package com.sh.data.engine.domain.shims.tidb.manager;

import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.GenericDbManager;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableSizeDomain;

import java.sql.Date;
import java.sql.*;
import java.util.*;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/7 10:27
 */
public class TidbManager extends GenericDbManager {

    public TidbManager(DbOptions options) {
        super(options);
    }

    static {
        String driverClass = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Could not load db driver class: " + driverClass);
        }
    }

    @Override
    public String getQueryVersionSql() {
        return "SELECT VERSION()";
    }

    @Override
    public boolean hasSchema() {
        return false;
    }

    @Override
    public boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException {
        List<FieldInfoDomain> fieldList = getFieldList(dbName, tableName);
        String fieldType = "";
        for (FieldInfoDomain i : fieldList) {
            if (Objects.equals(fieldName, i.getFieldName())) {
                fieldType = i.getFieldType();
                if ("varchar".equalsIgnoreCase(fieldType)) {
                    String columnSize = i.getColumnSize().toString();
                    fieldType = String.format("%s(%s)", fieldType, columnSize);
                }
                break;
            }
        }
        String sql =
            String.format(
                "ALTER TABLE %s.%s MODIFY COLUMN %s %s COMMENT '%s'",
                dbName, tableName, fieldName, fieldType, fieldDes);
        doAlertFiledDesc(sql);
        return true;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName) throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT "
                + "from information_schema.tables where TABLE_TYPE = 'BASE TABLE' AND table_schema = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableSizeDomain> getTableSizeList(String dbName, String schema) throws SQLException {
        String sql =
            "SELECT table_schema, table_name, "
                + "SUM(data_length + index_length) AS size "
                + "FROM information_schema.tables "
                + "WHERE table_schema = ? "
                + "GROUP BY table_schema, table_name";

        List<TableSizeDomain> list = new ArrayList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, dbName);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                TableSizeDomain domain = new TableSizeDomain();
                final String tableName = rs.getString("table_name");
                final long size = rs.getLong("size");
                final String tableSchema = rs.getString("table_schema");
                domain.setTableSize(size);
                domain.setTableName(tableName);
                list.add(domain);
            }
            return list;
        }
    }


    @Override
    public List<TableInfoDomain> getTableList(String dbName, String schema) throws SQLException {
        return getTableList(dbName);
    }

    private static TableInfoDomain getTableDomain(String dbName, ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Set<String> set = new HashSet<>();
        for (int index = 1; index <= meta.getColumnCount(); index++) {
            set.add(meta.getColumnLabel(index));
        }
        TableInfoDomain entity = new TableInfoDomain();
        entity.setDbName(dbName);

        if (set.contains("TABLE_NAME")) {
            String tblName = rs.getString("TABLE_NAME");
            entity.setTableName(tblName);
        }

        if (set.contains("TABLE_TYPE")) {
            String tableType = rs.getString("TABLE_TYPE");
            TableInfoDomain.TblType tblType = null;
            switch (tableType) {
                case "BASE TABLE":
                    tblType = TableInfoDomain.TblType.MANAGED_TABLE;
                    break;
                case "VIEW":
                    tblType = TableInfoDomain.TblType.VIEW;
                    break;
            }
            entity.setTblType(tblType);
        }

        // 视图的create_time和update_time都是空的
        if (set.contains("UPDATE_TIME")) {
            Date updateTime = rs.getDate("UPDATE_TIME");
            if (null == updateTime && set.contains("CREATE_TIME")) {
                updateTime = rs.getDate("CREATE_TIME");
            }
            if (null != updateTime) {
                entity.setLastDdlTime(updateTime.getTime());
            }
        }

        if (set.contains("TABLE_COMMENT")) {
            final String tableComment = rs.getString("TABLE_COMMENT");
            entity.setTableComment(tableComment);
        }

        return entity;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName) throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT "
                + "from information_schema.tables where TABLE_TYPE = 'VIEW' AND table_schema = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, String schema) throws SQLException {
        return getViewList(dbName);
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT "
                + "from information_schema.tables where table_schema = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException {
        return getTableAndViewList(dbName);
    }

    /**
     * @param dbName
     * @param tableName
     * @return
     * @throws SQLException
     */
    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException {
        String sql =
            "SELECT * FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName, tableName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String schema, String tableName)
        throws SQLException {
        return getTableInfoByTableName(dbName, tableName);
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String tableName) throws SQLException {
        String sql =
            "SELECT * FROM information_schema.columns WHERE table_schema = ? AND TABLE_NAME = ?";
        List<FieldInfoDomain> list = new LinkedList<>();
        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, dbName, tableName);
             ResultSet rs = pstmt.executeQuery()) {

            ResultSetMetaData metaData = rs.getMetaData();

            while (rs.next()) {
                FieldInfoDomain fieldInfoDomain = new FieldInfoDomain();

                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String fieldKey = metaData.getColumnName(i);
                    String fieldValue = rs.getString(i);
                    if ("COLUMN_NAME".equals(fieldKey)) {
                        fieldInfoDomain.setFieldName(fieldValue);
                    } else if ("COLUMN_TYPE".equals(fieldKey)) {
                        fieldInfoDomain.setFieldType(fieldValue);
                    } else if ("COLUMN_COMMENT".equals(fieldKey)) {
                        fieldInfoDomain.setComment(fieldValue);
                    } else if ("IS_NULLABLE".equals(fieldKey)) {
                        fieldInfoDomain.setNullable("YES".equals(fieldValue) ? Boolean.TRUE : Boolean.FALSE);
                    } else if ("numeric_precision".equals(fieldKey)) {
                        //            fieldInfoDomain.setFieldType(fieldInfoDomain.getFieldType() + "(" +
                        // fieldValue);
                    } else if ("numeric_scale".equals(fieldKey)) {
                    }
                }
                list.add(fieldInfoDomain);
            }
        }
        return list;
    }

    public static void main(String... args) throws SQLException {
        DbOptions dbOptions = new DbOptions();
        dbOptions.setUrl("jdbc:mysql://10.88.37.2:4000/geely?characterEncoding=utf8&useSSL=false");
        dbOptions.setUsername("root");
        dbOptions.setPassword("MynewPass@1.");
        TidbManager tidbManager = new TidbManager(dbOptions);
        List<TableInfoDomain> tableList = tidbManager.getTableList("geely");

        //    List<TableInfoDomain> tableList = mysqlManager.getTableList("hufu");
        //
        System.out.println(tableList);

        final List<FieldInfoDomain> fieldList = tidbManager.getFieldList("hufu", "test_model_table_77");
        for (FieldInfoDomain fieldInfoDomain : fieldList) {
            System.out.println(fieldInfoDomain);
        }
    }
}
